﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.IO;
using System.Data.SqlClient;
using Excel=Microsoft.Office.Interop.Excel;
using Missing = System.Reflection.Missing;


public partial class UserControl_ControlBaoCaoTienDoThucHienChiTieu : System.Web.UI.UserControl
{
    ClassExecute.ClassExecute dt;
    string str = webapp4U.Configuration.CurrentConnectionString;
    string error = "";
    
    protected void Page_Load(object sender, EventArgs e)
    {
        if (IsPostBack)
        {
            if (Application["db"] != null)
            {
                DataTable db = (DataTable)Application["db"];
                ASPxPivotGrid1.DataSource = db;
                ASPxPivotGrid1.DataBind();
            }
        }
        else
        {
            LoadMonthLy();
        }
    }
    protected void imgBtnAdd_Click(object sender, ImageClickEventArgs e)
    {
        dt = new ClassExecute.ClassExecute();
        try
        {
             DataTable db=null;
             if (RMonth.Checked)
             {
                 db = dt.Get_Data_DataTable(str, ref error, "k_getDSMonthly", CommandType.StoredProcedure,
                 new SqlParameter("@YYYYMM", DropMonthy.SelectedValue.ToString()));
             }
             else
             {
                 db = dt.Get_Data_DataTable(str, ref error, "k_getDSDaily", CommandType.StoredProcedure,
                 new SqlParameter("@Fromdate", txtFromDate.Text),
                 new SqlParameter("@Todate", txtToDate.Text),
                 new SqlParameter("@UserName",Session["user"].ToString()));
             }
            if (db.Rows.Count > 0)
            {

                ASPxPivotGrid1.DataSource = db;
                Application["db"] = db;
            }
            else
            {
                if (Application["db"] != null)
                {
                    Application["db"] = null;
                }
                ASPxPivotGrid1.DataSource = null;
            }
            ASPxPivotGrid1.DataBind();
            //RadioButtonList1.SelectedValue = "0";
            //ArrangeReport();
        }
        catch (Exception ex)
        {

            string method = System.Reflection.MethodBase.GetCurrentMethod().Name;
            dt.WriteErrorLog(Server.MapPath(webapp4U.Globals.LogUrl()), ex.Message, method);
        }
    }
    protected void imgBtnSave_Click(object sender, ImageClickEventArgs e)
    {
        try
        {
            using (MemoryStream filestream = new MemoryStream())
            {
                string contentType = "application/ms-excel";
               
                string fileName = "BaoCaoDoanhSoBanHang" + DateTime.Now.Year + "_" + DateTime.Now.Month + "_" + DateTime.Now.Day + ".xls";
                ASPxPivotGridExporter1.ExportToXls(filestream);
                byte[] buffer = filestream.GetBuffer();
                Response.Clear();
                Response.Buffer = false;
                Response.AppendHeader("Content-Type", contentType);
                Response.AppendHeader("Content-Transfer-Encoding", "binary");
                Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
                Response.BinaryWrite(buffer);
                Response.End();
            }
        }
        catch
        {

        }
    }
    protected void ImgBtnPivot_Click(object sender, ImageClickEventArgs e)
    {
        ExportExcelPivot();
       
    }
    void LoadMonthLy()
    {
        dt = new ClassExecute.ClassExecute();
        try
        {

            DataTable db = dt.Get_Data_DataTable(str, ref error, "k_getMonthly", CommandType.StoredProcedure);
            dt.BindDropDownList(db, DropMonthy, "ID", "Des");
        }
        catch (Exception ex)
        {

            string method = System.Reflection.MethodBase.GetCurrentMethod().Name;
            dt.WriteErrorLog(Server.MapPath(webapp4U.Globals.LogUrl()), ex.Message, method);
        }
    }
    public void Step1GenerateBindDataToSheet(string path)
    {
        if (File.Exists(path))
        {
            File.Delete(path);
        }
        Excel.Application m_objExcel;
        Excel.Workbooks m_objBooks;
        Excel._Workbook m_objBook;

        Excel.Sheets m_objSheets;
        Excel._Worksheet m_objSheet;

        Excel.Range m_objRange;

        object m_objOpt = System.Reflection.Missing.Value;

        Excel.QueryTables m_objQryTables;
        Excel._QueryTable m_objQryTable;

        // Start a new workbook in Excel.
        m_objExcel = new Excel.Application();
        m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
        m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt));

        // Create a QueryTable that starts at cell A1.
        m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
        m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));

        //Thêm title cho báo cáo
        Excel.Style style = m_objBook.Styles.Add("Style1", m_objOpt);
        style.Font.Bold = true;

        m_objSheet.Activate();
        Excel.Range range = ((Excel.Range)m_objSheet.Cells[1, 1]);
        range.Style = style;
        range.Value2 = "BÁO CÁO DOANH SỐ";
        Excel.Range range1 = ((Excel.Range)m_objSheet.Cells[2, 1]);
        range1.Style = style;
        range1.Value2 = "Ngày giờ lấy báo cáo :" + DateTime.Now.ToString();
        Excel.Range range2 = ((Excel.Range)m_objSheet.Cells[3, 1]);
        range2.Style = style;
        range2.Value2 = "Từ ngày: " + txtFromDate.Text + " đến ngày: " + txtToDate.Text;

        //
        m_objRange = m_objSheet.get_Range("A4", m_objOpt);
        m_objQryTables = m_objSheet.QueryTables;
        //@"OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;"
        m_objQryTable = (Excel._QueryTable)m_objQryTables.Add(@"OLEDB;Provider=SQLOLEDB.1;Persist Security Info=False;Integrated Security=SSPI;" + str, m_objRange, "select * from BaoCaoDoanhSoFimex");
        m_objQryTable.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows;
        m_objQryTable.Refresh(false);

        // Save the workbook and quit Excel.
        m_objBook.SaveAs(path, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
        m_objBook.Close(false, m_objOpt, m_objOpt);
        m_objExcel.Quit();

        //clear
        releaseObject(m_objSheet);
        releaseObject(m_objBook);
        releaseObject(m_objExcel);
    }
    void saveFile(System.Web.HttpResponse page, string PathfileName)
    {
        string filename = PathfileName;
        if (filename != "")
        {
            string path = filename;
            System.IO.FileInfo file = new System.IO.FileInfo(path);
            if (file.Exists)
            {
                page.Clear();
                page.AddHeader("Content-Disposition", "attachment; filename=" + file.Name);
                page.AddHeader("Content-Length", file.Length.ToString());
                page.ContentType = "application/octet-stream";
                page.WriteFile(file.FullName);
                page.End();

            }
        }

    }

    private void releaseObject(object obj)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            obj = null;
        }
        catch
        {

        }
        finally
        {
            GC.Collect();
        }
    }

    public void CreatePivot(string path)
    {
        if (File.Exists(path))
        {
            File.Delete(path);
        }
        string connection = @"OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;" + str;

        Excel.Application xlApp;
        Excel.Workbook exBook;
        Excel.Worksheet xlSheet = null;
        Excel.Range range;

        object misValue = System.Reflection.Missing.Value;

        xlApp = new Excel.ApplicationClass();
        exBook = xlApp.Workbooks.Add(misValue);



        xlSheet = (Excel.Worksheet)exBook.Worksheets.get_Item(1);
        xlSheet.Name = "DailyOffTake";
        Excel.PivotCache pivotCache = xlApp.ActiveWorkbook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlExternal, misValue);
        //GÁN CHUỔI KẾT NỐI
        pivotCache.Connection = connection;
        pivotCache.MaintainConnection = true;
        //GÁN CÂU LỆNH QUERY
        pivotCache.CommandText = "select * from BaoCaoDoanhSoFimex";
        pivotCache.CommandType = Excel.XlCmdType.xlCmdSql;
        Excel.PivotTables pivotTables = (Excel.PivotTables)xlSheet.PivotTables(misValue);
        Excel.PivotTable pivotTable = pivotTables.Add(pivotCache, xlApp.ActiveCell[20, 1], "Pivot", misValue, misValue);



        range = xlSheet.UsedRange;


        pivotTable.RowGrand = false;
        exBook.ShowPivotTableFieldList = false;
        range.NumberFormat = "#,##0.00_);[Red](#,##0.00)";

        exBook.SaveAs(path, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
        exBook.Close(true, misValue, misValue);

        xlApp.Quit();

        releaseObject(xlSheet);
        releaseObject(exBook);
        releaseObject(xlApp);
    }

    void PivotExcel()
    {

        if (File.Exists(Server.MapPath(Session["user"].ToString() + "_BCDoanhSoNgay.xls")))
        {
            File.Delete(Server.MapPath(Session["user"].ToString() + "_BCDoanhSoNgay.xls"));
        }


        // string connection = @"OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;" + str;
        string connection = @"OLEDB;Provider=SQLOLEDB.1;" + str;

        string cmd = "select * from BaoCaoDoanhSoFimex";
        Excel.Application excel = new Excel.Application();
        try
        {
            excel.Visible = false;
            excel.AlertBeforeOverwriting = false;
            Excel.Workbook workbook = excel.Workbooks.Add(Missing.Value);
            Excel.Worksheet sheet = (Excel.Worksheet)workbook.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            Excel.Style style = workbook.Styles.Add("Style1", Missing.Value);
            style.Font.Bold = true;
            style.Font.Size = 32;
            sheet.Activate();
            Excel.Range range = ((Excel.Range)sheet.Cells[1, 1]);
            range.Style = style;
            range.Value2 = "BÁO CÁO DOANH SỐ";
            Excel.Range range1 = ((Excel.Range)sheet.Cells[2, 1]);
            range1.Value2 = "Ngày giờ lấy báo cáo :" + DateTime.Now.ToString();
            Excel.Range range2 = ((Excel.Range)sheet.Cells[3, 1]);
            range2.Value2 = "Từ ngày :" + txtFromDate.Text + " đến ngày :" + txtToDate.Text;

            Excel.PivotCache pivotCache = excel.ActiveWorkbook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlExternal, Missing.Value);

            //GÁN CHUỔI KẾT NỐI
            pivotCache.Connection = connection;
            pivotCache.MaintainConnection = true;

            //GÁN CÂU LỆNH QUERY

            pivotCache.CommandText = cmd;
            pivotCache.CommandType = Excel.XlCmdType.xlCmdSql;
            Excel.PivotTables pivotTables = (Excel.PivotTables)sheet.PivotTables(Missing.Value);
            Excel.PivotTable pivotTable = pivotTables.Add(pivotCache, excel.ActiveCell[18, 1], "PivotTable1111", Missing.Value, Missing.Value);

            object[] noSub = { false, false, false, false, false, false, false, false, false, false, false, false };

            //ĐẶT TẠI VÙNG PAGE
            Excel.PivotField pageField5 = (Excel.PivotField)pivotTable.PivotFields("Region");
            pageField5.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField5.Caption = "Region";

            Excel.PivotField pageField6 = (Excel.PivotField)pivotTable.PivotFields("InvoiceID");
            pageField6.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField6.Caption = "InvoiceID";

            Excel.PivotField pageField7 = (Excel.PivotField)pivotTable.PivotFields("InvDate");
            pageField7.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField7.Caption = "InvDate";

            Excel.PivotField pageField8 = (Excel.PivotField)pivotTable.PivotFields("Customer of Fimex");
            pageField8.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField8.Caption = "Customer of Fimex";


            Excel.PivotField pageField9 = (Excel.PivotField)pivotTable.PivotFields("Sale supervisor");
            pageField9.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField9.Caption = "Sale supervisor";

            Excel.PivotField pageField10 = (Excel.PivotField)pivotTable.PivotFields("Product");
            pageField10.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField10.Caption = "Product";

            Excel.PivotField pageField11 = (Excel.PivotField)pivotTable.PivotFields("Quantity");
            pageField11.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField11.Caption = "Quantity";

            Excel.PivotField pageField12 = (Excel.PivotField)pivotTable.PivotFields("ChannelName");
            pageField12.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField12.Caption = "Channel";


            ////Dat vung data
            Excel.PivotField pageField15 = (Excel.PivotField)pivotTable.PivotFields("AmountTotal");
            pageField15.Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
            pageField15.Caption = "AmountTotal";

            ////ĐẶT TẠI VÙNG ROW

            Excel.PivotField pageField1 = (Excel.PivotField)pivotTable.PivotFields("Fimex");
            pageField1.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
            pageField1.Caption = "Head Office";

            Excel.PivotField pageField2 = (Excel.PivotField)pivotTable.PivotFields("Distributor");
            pageField2.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
            pageField2.Caption = "Distributor";

            Excel.PivotField pageField3 = (Excel.PivotField)pivotTable.PivotFields("Sale Man");
            pageField3.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
            pageField3.Caption = "TDV";

            Excel.PivotField pageField4 = (Excel.PivotField)pivotTable.PivotFields("Customer of Distributor");
            pageField4.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
            pageField4.Caption = "Customer of Distributor";

            workbook.SaveAs(Server.MapPath(Session["user"].ToString() + "_BCDoanhSoNgay.xls"), Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            workbook.Close(true, Missing.Value, Missing.Value);
            excel.Quit();

            saveFile(this.Response, Server.MapPath(Session["user"].ToString() + "_BCDoanhSoNgay.xls"));
        }
        catch
        {

        }
        finally
        {
            // Make sure we release the reference to the underlying COM object
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
        }
    }    
    //sắp xếp các field
    //void ArrangeReport()
    //{
    //    if (Application["db"] != null)
    //    {
    //        DataTable db = (DataTable)Application["db"];
    //        ASPxPivotGrid1.DataSource = db;
    //        ASPxPivotGrid1.DataBind();

    //        if (RadioButtonList1.SelectedValue == "0")
    //        {
    //            string[] arr1 = new string[] { "FM_00000002_NPP Nhật Minh Dương" ,"FM_00000003_NPP DKSH"};

    //            //fimex
    //            PivotGridField1.Area = DevExpress.XtraPivotGrid.PivotArea.RowArea;
    //            PivotGridField1.AreaIndex = 0;
    //            PivotGridField2.FilterValues.SetValues(arr1, DevExpress.Data.PivotGrid.PivotFilterType.Included, true);

    //            //Customer of Fimex
    //            PivotGridField9.Area = DevExpress.XtraPivotGrid.PivotArea.RowArea;
    //            PivotGridField9.AreaIndex = 1;

    //            //AmountTotal
    //            PivotGridField6.Area = DevExpress.XtraPivotGrid.PivotArea.DataArea;
    //            PivotGridField6.AreaIndex = 0;

    //            //Các field còn lại
    //            ArrangeFilterPage();

    //            //Distributor
    //            PivotGridField2.Area = DevExpress.XtraPivotGrid.PivotArea.FilterArea;
    //            PivotGridField2.AreaIndex = 8;

    //            //Customer of Distributor
    //            PivotGridField4.Area = DevExpress.XtraPivotGrid.PivotArea.FilterArea;
    //            PivotGridField4.AreaIndex = 8;

    //            //
    //            //for (int i = 0; i < ASPxPivotGrid1.Fields.Count; i++)
    //            //{
    //            //    if ((ASPxPivotGrid1.Fields[i].ToString() != "Fimex") || (ASPxPivotGrid1.Fields[i].ToString() != "AmountTotal") || (ASPxPivotGrid1.Fields[i].ToString() != "Customer of Fimex"))
    //            //    {
    //            //        ASPxPivotGrid1.Fields[i].Area = DevExpress.XtraPivotGrid.PivotArea.FilterArea;
    //            //        ASPxPivotGrid1.Fields[i].AreaIndex = i;
    //            //    }
    //            //}
              
    //        }
    //        else if (RadioButtonList1.SelectedValue == "1")
    //        {
    //            string[] arr1 = new string[] { "FM_00000002_NPP Nhật Minh Dương" };
    //            //Distributor
    //            PivotGridField2.Area = DevExpress.XtraPivotGrid.PivotArea.RowArea;
    //            PivotGridField2.AreaIndex = 0;
    //            PivotGridField2.FilterValues.SetValues(arr1, DevExpress.Data.PivotGrid.PivotFilterType.Excluded, true);

    //            //Customer of Distributor
    //            PivotGridField4.Area = DevExpress.XtraPivotGrid.PivotArea.RowArea;
    //            PivotGridField4.AreaIndex = 1;

    //            //AmountTotal
    //            PivotGridField6.Area = DevExpress.XtraPivotGrid.PivotArea.DataArea;
    //            PivotGridField6.AreaIndex = 0;

    //            //Các field còn lại
    //            ArrangeFilterPage();

    //            //Customer of Fimex
    //            PivotGridField9.Area = DevExpress.XtraPivotGrid.PivotArea.FilterArea;
    //            PivotGridField9.AreaIndex = 8;
    //            //Fimex
    //            PivotGridField1.Area = DevExpress.XtraPivotGrid.PivotArea.FilterArea;
    //            PivotGridField1.AreaIndex = 9;

    //            //for (int i = 0; i < ASPxPivotGrid1.Fields.Count; i++)
    //            //{
    //            //    if ((ASPxPivotGrid1.Fields[i].ToString() != "Distributor") || (ASPxPivotGrid1.Fields[i].ToString() != "AmountTotal") || (ASPxPivotGrid1.Fields[i].ToString() != "Customer of Distributor"))
    //            //    {
    //            //        ASPxPivotGrid1.Fields[i].Area = DevExpress.XtraPivotGrid.PivotArea.FilterArea;
    //            //        ASPxPivotGrid1.Fields[i].AreaIndex = i;
    //            //    }
    //            //}

    //        }
    //        else if (RadioButtonList1.SelectedValue == "2")
    //        {
    //            string[] arr1 = new string[] { "FM_00000003_NPP DKSH" };
    //            //Distributor
    //            PivotGridField2.Area = DevExpress.XtraPivotGrid.PivotArea.RowArea;
    //            PivotGridField2.AreaIndex = 0;
    //            PivotGridField2.FilterValues.SetValues(arr1, DevExpress.Data.PivotGrid.PivotFilterType.Excluded, true);

    //            //Customer of Distributor
    //            PivotGridField4.Area = DevExpress.XtraPivotGrid.PivotArea.RowArea;
    //            PivotGridField4.AreaIndex = 1;

    //            //AmountTotal
    //            PivotGridField6.Area = DevExpress.XtraPivotGrid.PivotArea.DataArea;
    //            PivotGridField6.AreaIndex = 0;

    //            //Các field còn lại
    //            ArrangeFilterPage();

    //            //Customer of Fimex
    //            PivotGridField9.Area = DevExpress.XtraPivotGrid.PivotArea.FilterArea;
    //            PivotGridField9.AreaIndex = 8;

    //            //Fimex
    //            PivotGridField1.Area = DevExpress.XtraPivotGrid.PivotArea.FilterArea;
    //            PivotGridField1.AreaIndex = 9;

    //            //for (int i = 0; i < ASPxPivotGrid1.Fields.Count; i++)
    //            //{
    //            //    if ((ASPxPivotGrid1.Fields[i].ToString() != "Distributor") || (ASPxPivotGrid1.Fields[i].ToString() != "AmountTotal") || (ASPxPivotGrid1.Fields[i].ToString() != "Customer of Distributor"))
    //            //    {
    //            //        ASPxPivotGrid1.Fields[i].Area = DevExpress.XtraPivotGrid.PivotArea.FilterArea;
    //            //        ASPxPivotGrid1.Fields[i].AreaIndex = i;
    //            //    }
    //            //}
    //        }
    //    }
        
    //}
    //void ArrangeFilterPage()
    //{
    //    //Sale Man
    //    PivotGridField3.Area = DevExpress.XtraPivotGrid.PivotArea.FilterArea;
    //    PivotGridField3.AreaIndex = 0;
    //    //Region
    //    PivotGridField5.Area = DevExpress.XtraPivotGrid.PivotArea.FilterArea;
    //    PivotGridField5.AreaIndex = 1;
    //    //InvoiceID
    //    PivotGridField7.Area = DevExpress.XtraPivotGrid.PivotArea.FilterArea;
    //    PivotGridField7.AreaIndex = 2;
    //    //InvDate
    //    PivotGridField8.Area = DevExpress.XtraPivotGrid.PivotArea.FilterArea;
    //    PivotGridField8.AreaIndex = 3;
    //    //Sale supervisor
    //    PivotGridField10.Area = DevExpress.XtraPivotGrid.PivotArea.FilterArea;
    //    PivotGridField10.AreaIndex = 4;
    //    //Product
    //    PivotGridField11.Area = DevExpress.XtraPivotGrid.PivotArea.FilterArea;
    //    PivotGridField11.AreaIndex = 5;
    //    //Quantity
    //    PivotGridField12.Area = DevExpress.XtraPivotGrid.PivotArea.FilterArea;
    //    PivotGridField12.AreaIndex = 6;
    //    //Channel
    //    PivotGridField13.Area = DevExpress.XtraPivotGrid.PivotArea.FilterArea;
    //    PivotGridField13.AreaIndex = 7;

    //}
    //main method
    void ExportExcelPivot()
    {
        string cmd = "select * from " + Session["user"].ToString() +"_ReportDoanhSo";

        string fileName = Session["user"].ToString() + "_ReportDoanhSo" + DateTime.Now.Year + "_" + DateTime.Now.Month + "_" + DateTime.Now.Day + ".xls";

        if (File.Exists(Server.MapPath(fileName)))
        {
            File.Delete(Server.MapPath(fileName));
        }
        string connection = @"OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;" + str;

        //doi tuong Trống để thêm  vào xlApp sau đó lưu lại sau
        object missValue = System.Reflection.Missing.Value;

        Excel.Application excel = new Excel.Application();

        excel.AlertBeforeOverwriting = false;

        Excel.Workbook workbook = excel.Workbooks.Add(Missing.Value);
        // Excel.Worksheet sheet =  (Excel.Worksheet)workbook.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
        Excel.Worksheet sheet = (Excel.Worksheet)workbook.ActiveSheet;
        sheet.Name = "DoanhSo";
        //su dung Sheet dau tien de thao tac
        //sheet = (Excel.Worksheet)workbook.Worksheets.get_Item(1);
        //không cho hiện ứng dụng Excel lên để tránh gây đơ máy
        excel.Visible = false;
        Excel.Style style = workbook.Styles.Add("Style1", Missing.Value);
        style.Font.Bold = true;
        style.Font.Size = 32;
      
        Excel.Range range = ((Excel.Range)sheet.Cells[1, 1]);
        range.Style = style;
        range.Value2 = "BÁO CÁO DOANH SỐ";
        Excel.Range range1 = ((Excel.Range)sheet.Cells[2, 1]);
        range1.Value2 = "Ngày giờ lấy báo cáo :" + DateTime.Now.ToString();
        Excel.Range range2 = ((Excel.Range)sheet.Cells[3, 1]);
        range2.Value2 = "Từ ngày :" + txtFromDate.Text + " đến ngày :" + txtToDate.Text;
        //


        //get a reference to the pivot chache -add a pivot table reference to the sheet
        Excel.PivotCache pch = excel.ActiveWorkbook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlExternal, missValue);

        //Activate the data sheet before adding the range to the pivot table
        // sheet.Activate();

        //GÁN CHUỔI KẾT NỐI
        pch.Connection = connection;
        pch.MaintainConnection = true;
        //GÁN CÂU LỆNH QUERY
        pch.CommandText = cmd;
        pch.CommandType = Excel.XlCmdType.xlCmdSql;
        Excel.PivotTables pivotTables = (Excel.PivotTables)sheet.PivotTables(Missing.Value);
        Excel.PivotTable pivotTable = pivotTables.Add(pch, excel.ActiveCell[20, 1], "PivotTable11", Missing.Value, Missing.Value);

        Excel.Range rangePivot = sheet.UsedRange;
        pivotTable.RowGrand = false;
        rangePivot.NumberFormat = "#,##0.00_);[Red](#,##0.00)";


        //Data
        Excel.PivotField TTTDV = ((Excel.PivotField)pivotTable.PivotFields("TTTDV"));
        TTTDV.Orientation = Excel.XlPivotFieldOrientation.xlDataField;

        Excel.PivotField TT = ((Excel.PivotField)pivotTable.PivotFields("TT"));
        TT.Orientation = Excel.XlPivotFieldOrientation.xlDataField;

        Excel.PivotField TTV = ((Excel.PivotField)pivotTable.PivotFields("TTV"));
        TTV.Orientation = Excel.XlPivotFieldOrientation.xlDataField;

        //fld.Caption = "AmountTotal";
        //fld.Function = Microsoft.Office.Interop.Excel.XlConsolidationFunction.xlSum;
        //fld.NumberFormat = "#,##0_);[Red](#,##0)";


        //Filter (Single Item row)
        Excel.PivotField InvoiceID = ((Excel.PivotField)pivotTable.PivotFields("InvoiceID"));
        InvoiceID.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
        InvoiceID.Caption = "Mã hóa đơn";

        Excel.PivotField InvDate = ((Excel.PivotField)pivotTable.PivotFields("InvDate"));
        InvDate.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
        InvDate.Caption = "Ngày hóa đơn";

        Excel.PivotField SubChannelName = ((Excel.PivotField)pivotTable.PivotFields("SubChannelName"));
        SubChannelName.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
        SubChannelName.Caption = "Sub channel";

        Excel.PivotField Province = ((Excel.PivotField)pivotTable.PivotFields("Province"));
        Province.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
        Province.Caption = "Tỉnh/Thành";

        Excel.PivotField Town = ((Excel.PivotField)pivotTable.PivotFields("Town"));
        Town.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
        Town.Caption = "Quận/Huyện";

        Excel.PivotField Region = ((Excel.PivotField)pivotTable.PivotFields("Region"));
        Region.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
        Region.Caption = "Region";

        Excel.PivotField Area = ((Excel.PivotField)pivotTable.PivotFields("Area"));
        Area.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
        Area.Caption = "Area";

        Excel.PivotField LoaiHD = ((Excel.PivotField)pivotTable.PivotFields("LoaiHD"));
        LoaiHD.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
        LoaiHD.Caption = "Loại HĐ";

        Excel.PivotField TenVT = ((Excel.PivotField)pivotTable.PivotFields("TenVT"));
        TenVT.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
        TenVT.Caption = "Sản phẩm(SKU)";

        Excel.PivotField Quantity = ((Excel.PivotField)pivotTable.PivotFields("Quantity"));
        Quantity.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
        Quantity.Caption = "Số lượng";

        Excel.PivotField DGLE = ((Excel.PivotField)pivotTable.PivotFields("DGLE"));
        DGLE.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
        DGLE.Caption = "Giá bán";

        Excel.PivotField ChannelSKU = ((Excel.PivotField)pivotTable.PivotFields("Channel(SKU)"));
        ChannelSKU.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
        ChannelSKU.Caption = "Channel(SKU)";

        Excel.PivotField GroupSKU = ((Excel.PivotField)pivotTable.PivotFields("Group(SKU)"));
        GroupSKU.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
        GroupSKU.Caption = "Group(SKU)";

       

        Excel.PivotField Distributor = ((Excel.PivotField)pivotTable.PivotFields("Distributor"));
        Distributor.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
        Distributor.Caption = "Nhà phân phối";

        Excel.PivotField HeadOffice = ((Excel.PivotField)pivotTable.PivotFields("HeadOffice"));
        HeadOffice.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
        HeadOffice.Caption = "Văn phòng đại diện";

        Excel.PivotField Channel = ((Excel.PivotField)pivotTable.PivotFields("Channel"));
        Channel.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
        Channel.Caption = "Channel";
       


        //save file
        workbook.SaveAs(Server.MapPath(fileName), Excel.XlFileFormat.xlWorkbookNormal, missValue, missValue, missValue, missValue, Excel.XlSaveAsAccessMode.xlExclusive, missValue, missValue, missValue, missValue, missValue);
        workbook.Close(true, missValue, missValue);
        excel.Quit();

        // release cac doi tuong COM
        releaseObject(pch);
        releaseObject(sheet);
        releaseObject(workbook);
        releaseObject(excel);

        saveFile(this.Response, Server.MapPath(fileName));
    }

    protected void RadioButtonList1_SelectedIndexChanged(object sender, EventArgs e)
    {
       // ArrangeReport();
    }
}
